from flask import render_template, redirect, url_for, request, flash
from flask_login import login_user, logout_user, login_required, current_user
from . import app
from .user import User
from app.collector.collect_all import collect_all

from .models import EtfFund, LofFund, MoneyFund, CollectLog
import pandas as pd
from datetime import datetime
@app.route('/')
def index():
    return render_template('index.html')

@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        if username == 'admin' and password == 'admin':
            user = User(id=1, username='admin')
            login_user(user)
            return redirect(url_for('dashboard'))
        else:
            flash('用户名或密码错误')
    return render_template('login.html')

@app.route('/logout')
@login_required
def logout():
    logout_user()
    return redirect(url_for('login'))

@app.route('/dashboard')
@login_required
def dashboard():
    # 获取统计数据
    stats = {
        'etf_count': EtfFund.query.count(),
        'lof_count': LofFund.query.count(),
        'money_count': MoneyFund.query.count(),
        'last_update': CollectLog.query.order_by(CollectLog.created_at.desc()).first()
    }
    return render_template('dashboard.html', stats=stats)

@app.route('/collect_logs')
@login_required
def collect_logs():
    logs = CollectLog.query.order_by(CollectLog.created_at.desc()).all()
    return render_template('collect_logs.html', logs=logs)


@app.route('/etf')
@login_required
def etf_list():
    # 获取查询参数
    search = request.args.get('search', '')
    sort_by = request.args.get('sort', 'updated_at')
    order = request.args.get('order', 'desc')
    
    # 构建查询
    query = EtfFund.query
    if search:
        query = query.filter(
            (EtfFund.name.contains(search)) |
            (EtfFund.code.contains(search)) |
            (EtfFund.tag.contains(search))
        )
    
    # 排序
    if order == 'desc':
        query = query.order_by(getattr(EtfFund, sort_by).desc())
    else:
        query = query.order_by(getattr(EtfFund, sort_by).asc())
    
    funds = query.all()
    return render_template('etf_list.html', funds=funds)

@app.route('/lof')
@login_required
def lof_list():
    search = request.args.get('search', '')
    sort_by = request.args.get('sort', 'updated_at')
    order = request.args.get('order', 'desc')
    
    query = LofFund.query
    if search:
        query = query.filter(
            (LofFund.name.contains(search)) |
            (LofFund.code.contains(search)) |
            (LofFund.tag.contains(search))
        )
    
    if order == 'desc':
        query = query.order_by(getattr(LofFund, sort_by).desc())
    else:
        query = query.order_by(getattr(LofFund, sort_by).asc())
    
    funds = query.all()
    return render_template('lof_list.html', funds=funds)

@app.route('/money')
@login_required
def money_list():
    search = request.args.get('search', '')
    sort_by = request.args.get('sort', 'updated_at')
    order = request.args.get('order', 'desc')
    
    query = MoneyFund.query
    if search:
        query = query.filter(
            (MoneyFund.name.contains(search)) |
            (MoneyFund.code.contains(search)) |
            (MoneyFund.tag.contains(search))
        )
    
    if order == 'desc':
        query = query.order_by(getattr(MoneyFund, sort_by).desc())
    else:
        query = query.order_by(getattr(MoneyFund, sort_by).asc())
    
    funds = query.all()
    return render_template('money_list.html', funds=funds)

@app.route('/analysis')
@login_required
def analysis():
    return render_template('analysis.html')
@app.route('/export/<fund_type>')
@login_required
def export_excel(fund_type):
    if fund_type == 'etf':
        data = EtfFund.query.all()
    elif fund_type == 'lof':
        data = LofFund.query.all()
    elif fund_type == 'money':
        data = MoneyFund.query.all()
    else:
        flash('类型错误')
        return redirect(url_for('dashboard'))
    
    # 转换为DataFrame
    df = pd.DataFrame([{
        column.name: getattr(item, column.name)
        for column in item.__table__.columns
    } for item in data])
    
    # 创建Excel
    output = pd.ExcelWriter(f'{fund_type}_funds_{datetime.now().strftime("%Y%m%d")}.xlsx')
    df.to_excel(output, index=False)
    output.save()
    
    return send_file(
        output.path,
        as_attachment=True,
        download_name=f'{fund_type}_funds_{datetime.now().strftime("%Y%m%d")}.xlsx'
    )

@app.route('/collect/<fund_type>')
@login_required
def collect_fund(fund_type):
    from .collector import collect_by_type
    try:
        success = collect_by_type(fund_type)
        if success:
            flash(f'{fund_type}数据采集成功')
        else:
            flash(f'{fund_type}数据采集失败')
    except ValueError as e:
        flash(str(e))
    return redirect(url_for('collect_logs'))

@app.route('/collect')
@login_required
def trigger_collect():
    success = collect_all()
    if success:
        flash('数据采集成功')
    else:
        flash('数据采集过程中存在错误')
    return redirect(url_for('collect_logs'))